package com.transcargo.request;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.transcargo.action.ServletParametersException;
import com.transcargo.action.ServletValidationException;
import com.transcargo.data.Cache;
import com.transcargo.db.db;
import com.transcargo.db.dbException;
import com.transcargo.posts.Cargo;
import com.transcargo.posts.Freight;
import com.transcargo.rpc.RPCOutput;
import com.transcargo.util.Normalizer;

import java.util.*;

public class SearchRequest extends Request {

        private static final int paging = 2;
        protected int resultCount = 0;
        private String date = "";
        private String city_value_from = "";
        private String regFrom = "";
        private String city_value_to = "";
        private String regTo = "";
        private int cargo_type = 0;
        private Float volume_from = 0f;
        private Float weight_from = 0f;
        private Float volume_to = 0f;
        private Float weight_to = 0f;
        private String what = "";
        private String tir = "";
        private String ekmt = "";
        private String adr = "";
        private String urgent = "";
        private String combined = "";
        private String passing = "";
        private String bid = "";
        private String permanent = "";
        protected String sfrom = "";
        private int cid_from = 0;
        private int rid_from = 0;
        private int ctid_from = 0;
        private int cid_to = 0;
        private int rid_to = 0;
        private int ctid_to = 0;
        private String loading = "";
        private String user_id = "";
        private int r_port = 0;
        protected String hash = "";
        private Cookie c = null;
        protected String xml = "";

        public String doSearchFreight(String query) throws dbException {
			Freight[] freights = null;
            String statementPrefix = "SELECT count(id) FROM posted_transport WHERE " + query;
            list = dbase.execute(statementPrefix);
            iter = list.iterator();
            String[] cnt = iter.next();
            resultCount = Integer.parseInt(cnt[0]);
            String xml =  RPCOutput.generate(false,"hash",hash);
            if (resultCount > 0) {

                String statement = "SELECT id, " +
                        "date_posted, " +
                        "date_load, " +
                        "date_unload, " +
                        "country_id_from, " +
                        "region_id_from, " +
                        "city_value_from, " +
                        "country_id_to, " +
                        "region_id_to, " +
                        "city_value_to, " +
                        "type, " +
                        "loading, " +
                        "height, " +
                        "width, " +
                        "length, " +
                        "tir, " +
                        "ekmt, " +
                        "adr, " +
                        "tonnage, " +
                        "volume, " +
                        "payment_bid, " +
                        "payment_type, " +
                        "payment_currency, " +
                        "urgent, " +
                        "urgent_time, " +
                        "passing, " +
                        "comments, " +
                        "permanent, " +
                        "user_id FROM posted_transport WHERE " + query + " AND visible=1 ORDER BY date_posted DESC LIMIT " + Integer.parseInt(sfrom) + "," + paging;

                list = dbase.execute(statement);
                if (list != null) iter = list.iterator();
                if (iter.hasNext()) {
                    freights = new Freight[list.size()];
                    int i = 0;
                    while (iter.hasNext()) {
                        String[] res = iter.next();
                        freights[i] = new Freight();
                        freights[i].id = Integer.parseInt(res[0]);
                        freights[i].date_posted = Normalizer.DenormalizeTime(res[1]);
                        freights[i].date_load = Normalizer.DenormalizeDate(res[2]);
                        freights[i].date_unload = Normalizer.DenormalizeDate(res[3]);
                        freights[i].country_from = res[4].equals("0")?"":Cache.getCountry(Integer.parseInt(res[4])).getName();
                        freights[i].region_from = (res[5].equals("0")||res[4].equals("0"))?"":Cache.getCountry(Integer.parseInt(res[4])).getRegion(Integer.parseInt(res[5])).getName();
                        freights[i].city_from = res[6];
                        freights[i].country_to = res[7].equals("0")?"":Cache.getCountry(Integer.parseInt(res[7])).getName();
                        freights[i].region_to = (res[7].equals("0")||res[8].equals("0"))?"":Cache.getCountry(Integer.parseInt(res[7])).getRegion(Integer.parseInt(res[8])).getName();
                        freights[i].city_to = res[9];
                        freights[i].type = Cache.getTransportTypes().get(res[10]);
                        StringTokenizer tok = new StringTokenizer(res[11],",");
                        int c = tok.countTokens();
                        freights[i].loading = new String[c];
                        for (int j = 0; j < c; j++) freights[i].loading[j] = Cache.getTransportLoadings().get(tok.nextToken());
                        freights[i].height = res[12];
                        freights[i].width = res[13];
                        freights[i].length = res[14];
                        freights[i].tir = res[15].equals("1");
                        freights[i].ekmt = res[16].equals("1");
                        freights[i].adr = res[17];
                        freights[i].tonnage = res[18];
                        freights[i].volume = res[19];
                        freights[i].bid = res[20];
                        freights[i].payment_type = Cache.getPaymentTypes().get(res[21]);
                        freights[i].currency = res[22].equals("0")?"0":Cache.getCurrencies().get(res[22]);
                        freights[i].urgent = res[23].equals("1");
                        freights[i].urgent_time = res[24];
                        freights[i].passing = res[25].equals("1");
                        freights[i].comments = res[26];
                        freights[i].permanent = res[27].equals("1");
                        freights[i].contact_visible = true;
                        freights[i].user_id = res[28];
                        int uid = Integer.parseInt(freights[i].user_id);
                        int contact_id = uid / 100000000;
                        uid = uid % 100000000;
                        System.out.println("contact_id = " + contact_id + ", uid = " + uid );
                        
                        ArrayList<String[]> list1 = null;
                        if (contact_id == 0) list1 = dbase.execute("SELECT registered_users.id, " +
                                "registered_users.organization, " +
                                "registered_users.address, " +
                                "registered_users.city, " +
                                "countries.name, " +
                                "registered_users.phone, " +
                                "registered_users.email, " +
                                "registered_users.web_url, " +
                                "registered_users.skype, " +
                                "registered_users.icq, " +
                                "registered_users.info " +
                                "FROM registered_users, countries WHERE countries.id=registered_users.country_id AND registered_users.id=" + freights[i].user_id);
                        else list1 = dbase.execute("SELECT registered_users.id, " +
                                "registered_users.organization, " +
                                "registered_users.address, " +
                                "registered_users.city, " +
                                "countries.name, " +
                                "registered_users.phone, " +
                                "registered_users.email, " +
                                "registered_users.web_url, " +
                                "registered_users.skype, " +
                                "registered_users.icq, " +
                                "registered_users.info " +
            					"contacts.name, " +
            					"contacts.phone1, " +
            					"contacts.phone2, " +
            					"contacts.phone3, " +
            					"contacts.email, " + 
            					"contacts.icq, " +
            					"contacts.skype, " +
            					"contacts.info, " +
            					"contacts.photo " +
            					"FROM registered_users, countries, contacts WHERE countries.id=registered_users.country_id AND contacts.user_id = registered_users.id AND registered_users.id=" + uid + " AND contacts.id=" + contact_id);
                        if (list1 != null) {
                            Iterator<String[]>iter1 = list1.iterator();
                            if (iter1.hasNext()) {
                                String[] rec = iter1.next();

                                freights[i].user_id = rec[0];
                                freights[i].org_name = rec[1];
                                freights[i].org_address = rec[2];
                                freights[i].org_city = rec[3];
                                freights[i].country = rec[4];
                                freights[i].org_phone = rec[5];
                                freights[i].org_email = rec[6];
                                freights[i].org_url = rec[7];
                                freights[i].org_skype = rec[8];
                                freights[i].org_icq = rec[9];
                                freights[i].org_info = rec[10];
                                if (contact_id > 0) {
                                    freights[i].contact_name = rec[11];
                                    freights[i].contact_phone1 = rec[12];
                                    freights[i].contact_phone2 = rec[13];
                                    freights[i].contact_phone3 = rec[14];
                                    freights[i].contact_email = rec[15];
                                    freights[i].contact_icq = rec[16];
                                    freights[i].contact_skype = rec[17];
                                    freights[i].contact_info = rec[18];
                                    freights[i].contact_photo = rec[19];
                                }
                            }
                        }
                        i++;
                    }
                }
                if (hash == null || hash.length() == 0)
				xml =  RPCOutput.generate(freights);
				else xml =  RPCOutput.generate(freights,"hash",hash, resultCount, Integer.parseInt(sfrom), paging);
			}			
            return xml;
		}
		public void SearchFreight() throws ServletRequestException
		{
			date = date.replace("'", "");
			city_value_from = city_value_from.replace("'", "");
			city_value_to = city_value_to.replace("'", "");
			try {
                String statement = "";
                if (permanent.equals("on")) {
                    statement = statement + "permanent=1";
                } else {
                    if (!date.equals("CURRENT_DATE")) date = "'" + Normalizer.NormalizeDate(date) + "'";
                    statement = statement + "((date_load<=" + date;
                    statement = statement + " AND date_unload>=" + date+")";
                    statement = statement + " OR permanent=1)";
                }
                if (cid_from > 0) {
			    	statement = statement + " AND country_id_from=" + cid_from;
			    	if (rid_from > 0) {
				    	statement = statement + " AND region_id_from=" + rid_from;
			    	}
			    	if (ctid_from > 0) {
			    		if (city_value_from.length() > 0) {
					    	statement = statement + " AND (city_id_from=" + ctid_from + " OR city_value_from='" + city_value_from + "')";
			    		} else {
			    			statement = statement + " AND city_id_from=" + ctid_from;
			    		}
			    	}
			    } else if (city_value_from.length() > 0) {
			    	statement = statement + " AND city_value_from='" + city_value_from + "'";
			    }
			    if (cid_to > 0) {
			    	statement = statement + " AND country_id_to=" + cid_to;
			    	if (rid_from > 0) {
				    	statement = statement + " AND region_id_to=" + rid_to;
			    	}
			    	if (ctid_to > 0) {
			    		if (city_value_to.length() > 0) {
					    	statement = statement + " AND (city_id_to=" + ctid_to + " OR city_value_to='" + city_value_to + "')";
			    		} else {
			    			statement = statement + " AND city_id_to=" + ctid_to;
			    		}
			    	}
			    } else if (city_value_to.length() > 0) {
			    	statement = statement + " AND city_value_to='" + city_value_to + "'";
			    }
			    if (cargo_type > 0) {
			    	statement = statement + " AND type=" + cargo_type;
			    }
                if (weight_from > 0) {
                    statement = statement + " AND tonnage>=" + weight_from;
                }
                if (weight_to > 0) {
                    statement = statement + " AND tonnage<=" + weight_to;
                }
                if (volume_from > 0) {
                    statement = statement + " AND volume>=" + volume_from;
                }
                if (volume_to > 0) {
                    statement = statement + " AND volume<=" + volume_to;
                }
                if (tir.equals("on")) {
			    	statement = statement + " AND tir=1";
			    }
			    if (ekmt.equals("on")) {
			    	statement = statement + " AND ekmt=1";
			    }
			    if (!adr.equals("")) {
			    	statement = statement + " AND adr>=" + adr;
			    }
			    if (urgent.equals("on")) {
			    	statement = statement + " AND urgent=1";
			    }
			    if (passing.equals("on")) {
			    	statement = statement + " AND passing=1";
			    }
			    if (bid.equals("on")) {
			    	statement = statement + " AND payment_bid>0";
			    }
			    if (user_id.length() > 0) {
			    	int Uid = Integer.parseInt(user_id);
			    	statement = statement + " AND (user_id mod 100000000)=" + Uid;
			    }
			    if (loading.length() > 0) {
			    	StringTokenizer tok = new StringTokenizer(loading,",");
			    	statement = statement + " AND (";
			    	while (tok.hasMoreTokens()) {
			    		String t = tok.nextToken();
			    		statement = statement + "loading like '%" + t + "%'";
			    		if (tok.hasMoreTokens()) statement = statement + " OR ";
			    	}
			    	statement = statement + ")";
			    }

                // finished forming string
                String hash = "";
                Cookie c = null;

                if (cookies != null) {
                  for (int i = 0; i < cookies.length; i++) {
                    if (cookies[i].getName().equals("searchHash")) {
                      c = cookies[i];
                    }
                  }
                }
                if (c != null) {
                    hash = c.getValue();
                }
                if (hash.length() == 0) {
                    Random rand = new Random();
	                Date dateS = new Date();
	                rand.setSeed(r_port*17600000 + dateS.getTime());
	                for (int j = 0; j < 12; j++) hash = hash + (char)('A' + rand.nextInt(26));
                } else {
                    db.PreStatement pre = dbase.CreateStatement("DELETE FROM hashedqueries WHERE hash=?");
                    pre.Insert(hash);
                    dbase.executeUpdate(pre);
                }
                xml = doSearchFreight(statement);
	            db.PreStatement pre = dbase.CreateStatement("INSERT INTO hashedqueries (hash, date, type, query, count) values (?, CURRENT_TIMESTAMP, ?, HEX(?), ?)");
	            pre.Insert(hash);
	            pre.Insert(0);
	            pre.Insert(statement);
	            pre.Insert(resultCount);
	            dbase.executeUpdate(pre);
			} catch (java.sql.SQLException e) {
				System.out.println("IOException: " + e.getMessage());
                throw new ServletRequestException();
            } catch (dbException e) {
				System.out.println("DB Exception during search: " + e.getMessage());
                throw new ServletRequestException();
            }
        }

		public String doSearchCargo(String query) throws dbException
		{
			Cargo[] cargos = null;
            String statementPrefix = "SELECT count(id) FROM posted_cargo WHERE " + query;
            list = dbase.execute(statementPrefix);
            iter = list.iterator();
            String[] cnt = iter.next();
            resultCount = Integer.parseInt(cnt[0]);
            String xml =  RPCOutput.generate(false);
            if (resultCount > 0) {

                String statement = "SELECT id, " +
                        "date_posted, " +
                        "date_load, " +
                        "date_unload, " +
                        "country_id_from, " +
                        "region_id_from, " +
                        "city_value_from, " +
                        "country_id_to, " +
                        "region_id_to, " +
                        "city_value_to, " +
                        "cargo, " +
                        "cars, " +
                        "type, " +
                        "loading, " +
                        "tir, " +
                        "adr, " +
                        "weight, " +
                        "volume, " +
                        "payment_bid, " +
                        "payment_type, " +
                        "payment_currency, " +
                        "urgent, " +
                        "urgent_time, " +
                        "combined, " +
                        "passing, " +
                        "comments, " +
                        "permanent, " +
                        "times, " +
                        "customs, " +
                        "user_id FROM posted_cargo WHERE " + query + " AND visible=1 ORDER BY date_posted DESC LIMIT " + Integer.parseInt(sfrom) + "," + paging;

                list = dbase.execute(statement);
                if (list != null) iter = list.iterator();
                if (iter.hasNext()) {
                	cargos = new Cargo[list.size()];
                    int i = 0;
                    while (iter.hasNext()) {
                        String[] res = iter.next();
                        cargos[i] = new Cargo();
                        cargos[i].id = Integer.parseInt(res[0]);
                        cargos[i].date_posted = Normalizer.DenormalizeTime(res[1]);
                        cargos[i].date_load = Normalizer.DenormalizeDate(res[2]);
                        cargos[i].date_unload = Normalizer.DenormalizeDate(res[3]);
                        cargos[i].country_from = res[4].equals("0")?"":Cache.getCountry(Integer.parseInt(res[4])).getName();
                        cargos[i].region_from = (res[5].equals("0")||res[4].equals("0"))?"":Cache.getCountry(Integer.parseInt(res[4])).getRegion(Integer.parseInt(res[5])).getName();
                        cargos[i].city_from = res[6];
                        cargos[i].country_to = res[7].equals("0")?"":Cache.getCountry(Integer.parseInt(res[7])).getName();
                        cargos[i].region_to = (res[7].equals("0")||res[8].equals("0"))?"":Cache.getCountry(Integer.parseInt(res[7])).getRegion(Integer.parseInt(res[8])).getName();
                        cargos[i].city_to = res[9];
                        cargos[i].cargo = res[10];
                        cargos[i].cars = res[11];
                        cargos[i].type = Cache.getTransportTypes().get(res[12]);
                        StringTokenizer tok = new StringTokenizer(res[13],",");
                        int c = tok.countTokens();
                        cargos[i].loading = new String[c];
                        for (int j = 0; j < c; j++) cargos[i].loading[j] = Cache.getTransportLoadings().get(tok.nextToken());
                        cargos[i].tir = res[14].equals("1");
                        cargos[i].adr = res[15];
                        cargos[i].weight = res[16];
                        cargos[i].volume = res[17];
                        cargos[i].bid = res[18];
                        cargos[i].payment_type = Cache.getPaymentTypes().get(res[19]);
                        cargos[i].currency = res[20].equals("0")?"0":Cache.getCurrencies().get(res[20]);
                        cargos[i].urgent = res[21].equals("1");
                        cargos[i].urgent_time = res[22];
                        cargos[i].combined = res[23].equals("1");
                        cargos[i].passing = res[24].equals("1");
                        cargos[i].comments = res[25];
                        cargos[i].permanent = res[26].equals("1");
                        cargos[i].times = res[27];
                        cargos[i].customs = res[28].equals("1");
                        cargos[i].contact_visible = true;
                        cargos[i].user_id = res[29];
                        int uid = Integer.parseInt(cargos[i].user_id);
                        int contact_id = uid / 100000000;
                        uid = uid % 100000000;
                        
                        ArrayList<String[]> list1 = null;
                        if (contact_id == 0) list1 = dbase.execute("SELECT registered_users.id, " +
                                "registered_users.organization, " +
                                "registered_users.address, " +
                                "registered_users.city, " +
                                "countries.name, " +
                                "registered_users.phone, " +
                                "registered_users.email, " +
                                "registered_users.web_url, " +
                                "registered_users.skype, " +
                                "registered_users.icq, " +
                                "registered_users.info " +
                                "FROM registered_users, countries WHERE countries.id=registered_users.country_id AND registered_users.id=" + cargos[i].user_id);
                        else list1 = dbase.execute("SELECT registered_users.id, " +
                                "registered_users.organization, " +
                                "registered_users.address, " +
                                "registered_users.city, " +
                                "countries.name, " +
                                "registered_users.phone, " +
                                "registered_users.email, " +
                                "registered_users.web_url, " +
                                "registered_users.skype, " +
                                "registered_users.icq, " +
                                "registered_users.info " +
            					"contacts.name, " +
            					"contacts.phone1, " +
            					"contacts.phone2, " +
            					"contacts.phone3, " +
            					"contacts.email, " + 
            					"contacts.icq, " +
            					"contacts.skype, " +
            					"contacts.info, " +
            					"contacts.photo " +
            					"FROM registered_users, countries, contacts WHERE countries.id=registered_users.country_id AND contacts.user_id = registered_users.id AND registered_users.id=" + uid + " AND contacts.id=" + contact_id);
                        if (list1 != null) {
                            Iterator<String[]>iter1 = list1.iterator();
                            if (iter1.hasNext()) {
                                String[] rec = iter1.next();

                                cargos[i].user_id = rec[0];
                                cargos[i].org_name = rec[1];
                                cargos[i].org_address = rec[2];
                                cargos[i].org_city = rec[3];
                                cargos[i].country = rec[4];
                                cargos[i].org_phone = rec[5];
                                cargos[i].org_email = rec[6];
                                cargos[i].org_url = rec[7];
                                cargos[i].org_skype = rec[8];
                                cargos[i].org_icq = rec[9];
                                cargos[i].org_info = rec[10];
                                if (contact_id > 0) {
                                	cargos[i].contact_name = rec[11];
                                	cargos[i].contact_phone1 = rec[12];
                                	cargos[i].contact_phone2 = rec[13];
                                	cargos[i].contact_phone3 = rec[14];
                                	cargos[i].contact_email = rec[15];
                                	cargos[i].contact_icq = rec[16];
                                	cargos[i].contact_skype = rec[17];
                                	cargos[i].contact_info = rec[18];
                                	cargos[i].contact_photo = rec[19];
                                }
                            }
                        }
                        i++;
                    }
                }
                if (hash == null || hash.length() == 0)
				xml =  RPCOutput.generate(cargos);
				else xml =  RPCOutput.generate(cargos,"hash",hash, resultCount, Integer.parseInt(sfrom), paging);
			}			
            return xml;
        }

        public void SearchCargo() throws ServletRequestException
		{
			PrintWriter out = null;
			date = date.replace("'", "");
			city_value_from = city_value_from.replace("'", "");
			city_value_to = city_value_to.replace("'", "");
			try {
                String statement = "";
                if (!date.equals("CURRENT_DATE")) date = "'" + Normalizer.NormalizeDate(date) + "'";
                if (permanent.equals("on")) {
                    statement = statement + "permanent=1";
                } else {
                    statement = statement + "((date_load<=" + date;
                    statement = statement + " AND date_unload>=" + date+")";
                    statement = statement + " OR permanent=1)";
                }
                if (cid_from > 0) {
			    	statement = statement + " AND country_id_from=" + cid_from;
			    	if (rid_from > 0) {
				    	statement = statement + " AND region_id_from=" + rid_from;
			    	}
			    	if (ctid_from > 0) {
			    		if (city_value_from.length() > 0) {
					    	statement = statement + " AND (city_id_from=" + ctid_from + " OR city_value_from='" + city_value_from + "')";
			    		} else {
			    			statement = statement + " AND city_id_from=" + ctid_from;
			    		}
			    	}
			    } else if (city_value_from.length() > 0) {
			    	statement = statement + " AND city_value_from='" + city_value_from + "'";
			    }
			    if (cid_to > 0) {
			    	statement = statement + " AND country_id_to=" + cid_to;
			    	if (rid_from > 0) {
				    	statement = statement + " AND region_id_to=" + rid_to;
			    	}
			    	if (ctid_to > 0) {
			    		if (city_value_to.length() > 0) {
					    	statement = statement + " AND (city_id_to=" + ctid_to + " OR city_value_to='" + city_value_to + "')";
			    		} else {
			    			statement = statement + " AND city_id_to=" + ctid_to;
			    		}
			    	}
			    } else if (city_value_to.length() > 0) {
			    	statement = statement + " AND city_value_to='" + city_value_to + "'";
			    }
			    if (cargo_type > 0) {
			    	statement = statement + " AND type=" + cargo_type;
			    }
                if (weight_from > 0) {
                    statement = statement + " AND weight>=" + weight_from;
                }
                if (weight_to > 0) {
                    statement = statement + " AND weight<=" + weight_to;
                }
                if (volume_from > 0) {
                    statement = statement + " AND volume>=" + volume_from;
                }
                if (volume_to > 0) {
                    statement = statement + " AND volume<=" + volume_to;
                }
			    if (!adr.equals("")) {
			    	statement = statement + " AND adr>=" + Integer.parseInt(adr);
			    }
			    if (urgent.equals("on")) {
			    	statement = statement + " AND urgent=1";
			    }
			    if (passing.equals("on")) {
			    	statement = statement + " AND passing=1";
			    }
			    if (combined.equals("on")) {
			    	statement = statement + " AND combined=1";
			    }
			    if (bid.equals("on")) {
			    	statement = statement + " AND payment_bid>0";
			    }
			    if (loading.length() > 0) {
			    	StringTokenizer tok = new StringTokenizer(loading,",");
			    	statement = statement + " AND (";
			    	while (tok.hasMoreTokens()) {
			    		String t = tok.nextToken();
			    		statement = statement + "loading like '%" + t + "%'";
			    		if (tok.hasMoreTokens()) statement = statement + " OR ";
			    	}
			    	statement = statement + ")";
			    }
                // finished forming string

                if (cookies != null) {
                  for (int i = 0; i < cookies.length; i++) {
                    if (cookies[i].getName().equals("searchHash")) {
                        c = cookies[i];
                        break;
                    }
                  }
                }
                if (c != null) hash = c.getValue();
                if (hash.length() == 0) {
                    Random rand = new Random();
	                Date dateS = new Date();
	                rand.setSeed(r_port*17600000 + dateS.getTime());
	                for (int j = 0; j < 12; j++) hash = hash + (char)('A' + rand.nextInt(26));
                } else {
                    db.PreStatement pre = dbase.CreateStatement("DELETE FROM hashedqueries WHERE hash=?");
                    pre.Insert(hash);
                    dbase.executeUpdate(pre);
                }
                xml = doSearchCargo(statement);
	            db.PreStatement pre = dbase.CreateStatement("INSERT INTO hashedqueries (hash, date, type, query, count) values (?, CURRENT_TIMESTAMP, ?, HEX(?), ?)");
	            pre.Insert(hash);
	            pre.Insert(1);
	            pre.Insert(statement);
	            pre.Insert(resultCount);
	            dbase.executeUpdate(pre);
			} catch (dbException e) {
				System.out.println("DB Exception during search: " + e.getMessage());
                throw new ServletRequestException();
			} catch (java.sql.SQLException e) {
				System.out.println("SQL Exception during search: " + e.getMessage());
                throw new ServletRequestException();
			}
        }
	

    private void servletGetValues(HttpServletRequest request) throws ServletParametersException, ServletException {
        date = request.getParameter("date");
        city_value_from = request.getParameter("from");
        regFrom = request.getParameter("regfrom");
        city_value_to = request.getParameter("to");
        regTo = request.getParameter("regto");
        String c_t = request.getParameter("cargo_type");
        String v_f = request.getParameter("volume_from");
        String w_f = request.getParameter("weight_from");
        String v_t = request.getParameter("volume_to");
        String w_t = request.getParameter("weight_to");
        what = request.getParameter("what");
        tir = request.getParameter("tir");
        ekmt = request.getParameter("ekmt");
        adr = request.getParameter("adr");
        urgent = request.getParameter("urgent");
        combined = request.getParameter("combined");
        passing = request.getParameter("passing");
        bid = request.getParameter("bid");
        permanent = request.getParameter("permanent");
        sfrom = request.getParameter("sfrom");
        user_id = request.getParameter("uid");

        if (date == null || date.length()==0) date="CURRENT_DATE";
        if (city_value_from == null) city_value_from="";
        if (regFrom == null) regFrom="";
        if (city_value_to == null) city_value_to="";
        if (regTo == null) regTo="";
        if (c_t == null || c_t.length()==0) c_t="0";
        if (v_f == null || v_f.length()==0) v_f="0";
        if (w_f == null || w_f.length()==0) w_f="0";
        if (v_t == null || v_t.length()==0) v_t="200";
        if (w_t == null || w_t.length()==0) w_t="200";
        if (what == null) what="1";
        if (tir == null) tir="";
        if (ekmt == null) ekmt="";
        if (adr == null) adr="";
        if (urgent == null) urgent="";
        if (combined == null) combined="";
        if (bid == null) bid="";
        if (passing == null) passing="";
        if (permanent == null) permanent="";
        if (user_id == null) user_id="";
        if (sfrom == null || sfrom.length() == 0) sfrom="0";

        StringTokenizer tok1 = new StringTokenizer(regFrom,"-");
        StringTokenizer tok2 = new StringTokenizer(regTo,"-");
        try {
            switch (tok1.countTokens()) {
                case 3:
                    cid_from = Integer.parseInt(tok1.nextToken());
                    rid_from = Integer.parseInt(tok1.nextToken());
                    ctid_from = Integer.parseInt(tok1.nextToken());
                    break;
                case 2:
                    cid_from = Integer.parseInt(tok1.nextToken());
                    rid_from = Integer.parseInt(tok1.nextToken());
                    break;
                case 1:
                    cid_from = Integer.parseInt(tok1.nextToken());
                    break;
            }
            switch (tok2.countTokens()) {
                case 3:
                    cid_to = Integer.parseInt(tok2.nextToken());
                    rid_to = Integer.parseInt(tok2.nextToken());
                    ctid_to = Integer.parseInt(tok2.nextToken());
                    break;
                case 2:
                    cid_to = Integer.parseInt(tok2.nextToken());
                    rid_to = Integer.parseInt(tok2.nextToken());
                    break;
                case 1:
                    cid_to = Integer.parseInt(tok2.nextToken());
                    break;
            }
        } catch (NumberFormatException e) {
            throw new ServletParametersException();
        }
        if (w_f.length() > 0) w_f = w_f.replace(",", ".");
        if (v_f.length() > 0) v_f = v_f.replace(",", ".");
        if (w_t.length() > 0) w_t = w_t.replace(",", ".");
        if (v_t.length() > 0) v_t = v_t.replace(",", ".");
        Iterator<String> it = Cache.getTransportLoadings().keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            String b = request.getParameter("load" + key);
            if (b != null && b.equals("on")) loading = loading.concat(loading.length() > 0 ? "," + key : key);
        }
        r_port = request.getRemotePort();
        cargo_type = Integer.parseInt(c_t);
        volume_from = Float.parseFloat(v_f);
        weight_from = Float.parseFloat(w_f);
        volume_to = Float.parseFloat(v_t);
        weight_to = Float.parseFloat(w_t);

    }
    private void servletValidateValues() throws ServletValidationException {
    }

    private void servletExecuteRequest() throws ServletRequestException {
        if (what.equals("1")) SearchFreight();
        else if (what.equals("2")) SearchCargo();
    }

    public void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException {
        PrintWriter out = null;
		try {
            out = response.getWriter();

            servletGetValues(request);
            servletValidateValues();
            servletExecuteRequest();
            out.println(xml);
	    } catch (ServletParametersException e) {
           sendRedirect(response, "index.jsp");
        }  catch (ServletValidationException e) {
            sendError(response, new Exception(e.getMessage()));
        }  catch (ServletRequestException e) {
            String xml =  RPCOutput.generate(false);
            out.println(xml);
        }  catch (IOException e) {
            System.out.println("IOException: " + e.getMessage());
        }
    }
}
